﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetDocumentStatusByStaff_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetDocumentStatusByStaff_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetDocumentStatusByStaff_V2]
    @StaffName nvarchar(300),
    @DocID int
/*

============================================================
功能:    得到个人对文档的操作状态
参数:
    @StaffName nvarchar    :    操作人
    @DocID int        :    文档ID
注释：
    --------------------
    操作状态        意义
    --------------------
    0        签收
    1        一般待批
    2        撤回
    3        查看
    4        会签待批

    --------------------
    文档状态        意义
    --------------------
    0        未签
    1        待批
    2        会签中
    3        完成
    4        拒绝

============================================================

*/
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Status int;
DECLARE @staff_id int;

-- Get the id of the specified staff
SELECT @staff_id = staff_id FROM dbo.UDS_Staff WHERE staff_name = @StaffName;
IF @@ROWCOUNT <> 1
BEGIN
    RAISERROR('The specified staff "%s" does not exist.', 16, 1, @StaffName);
    RETURN -1;
END

-- Get the status of the document
SELECT @Status = Status FROM dbo.UDS_Flow_Status WHERE staff_id = @staff_id AND doc_id = @DocID
IF ( @@ROWCOUNT = 0 )
BEGIN
    SET @Status = 3 /* 查看 */
END
ELSE
BEGIN
    --对于未签文档，操作是签收
    If @Status = 0
    BEGIN
        --判断该文档当前环节是否会签
        IF EXISTS(SELECT 1 FROM dbo.UDS_Flow_Document WHERE Doc_ID = @DocID AND (Doc_Status =2 OR Doc_Status =0) )            
            SET @Status = 0
        ELSE
            SET @Status = 3 /* 查看 */
    END

    --对于已签收文档，操作是待批
    If @Status = 1
    BEGIN
        --判断该文档当前环节是否会签
        IF EXISTS(SELECT 1 FROM uds_flow_document WHERE Doc_ID = @DocID AND (doc_status =2 OR doc_status =0) )
            SET @Status = 4
        ELSE
            SET @Status = 1
    END

    --对于已批阅的文档，操作是查看
    If @Status = 2
        SET @Status = 3 /* 查看 */
END

RETURN @Status

END
GO
